﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: May 19 2016
-- Description:	
-- =============================================
CREATE FUNCTION [dbo].[DataConversion_StudentInfo] 
(

)
RETURNS 
@JSONtoWrite TABLE (JSONvalue nvarchar(MAX),EntityID int,JSONvalue2 nvarchar(MAX),WriteType nvarchar(100))
AS
BEGIN
	--**********************************************************************************************
	--PRINT 'Insert new students into the database.'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,JSONvalue2,WriteType)
	SELECT				'{"EntityID":"' + '-1'
					  + '","PersonID":"' + CAST(S.FriendlyID AS NVARCHAR)
					  + COALESCE('","FirstName":"' + S.FirstName,'')
					  + COALESCE('","MiddleName":"' + S.MiddleName,'')
					  + COALESCE('","LastName":"' + S.LastName,'')
					  + COALESCE('","PreferredName":"' + S.PreferredName,'')
					  + COALESCE('","GradYear":"' + CAST(S.GradYear AS NVARCHAR),'')
					  + '","Gender":"' + (CASE WHEN S.Gender = 0 THEN 'Female' ELSE 'Male' END)
					  + COALESCE('","Logon":"' + S.Logon,'')
					  + '","EntityType":"' + 'Person' + '"'
					  -- If the entity is to be created, it also needs to have a relationship
					  -- If they have an admissions request, use that.
					  -- If they have an enrollment record and no admissions request, use that.
					  + CASE WHEN NOT ADM.StudentID IS NULL THEN ''
					  --',"ShortRelationshipName":"' + 'APPLIED' + '","Entity2ID":"@E2ID"'
						ELSE CASE WHEN EnTerm = 1 THEN ',"Entity2ID":"10000","ShortRelationshipName":"' + 'STUDENT' + '","StartDate":"' 
							+ '08/10/' + CAST(EnYear AS nvarchar) + '","EndDate":"' + '12/15/' + CAST(EnYear AS nvarchar) + '"'
						ELSE CASE WHEN EnTerm = 2 THEN ',"Entity2ID":"10000","ShortRelationshipName":"' + 'STUDENT' + '","StartDate":"' 
							+ '12/16/' + CAST(EnYear AS nvarchar) + '","EndDate":"' + '04/10/' + CAST(EnYear+1 AS nvarchar) + '"'
						ELSE ',"Entity2ID":"10000","ShortRelationshipName":"' + 'STUDENT' + '","StartDate":"' 
							+ '04/11/' + CAST(EnYear+1 AS nvarchar) + '","EndDate":"' + '07/25/' + CAST(EnYear+1 AS nvarchar) + '"'
						END END END
					  + '}' AS JSONvalue,-1 as EntityID,
					  CASE WHEN NOT ADM.StudentID IS NULL THEN '{"EntityType":"AdmissionsRequest"'
									  + ',"EntityID":"' + '-1' + '"'
									  + ',"LDHistory":"' + CASE WHEN [Learning Difficulty History] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"ADDHistory":"' + CASE WHEN [ADD History] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"DisciplineHistory":"' + CASE WHEN [Discipline History] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"SpecialMeds":"' + CASE WHEN [Special Medication] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"BedWetHistory":"' + CASE WHEN [Bed Wetting History] = 1 THEN 'True' ELSE 'False' END + '"'
									  -- The original question is english first language, so 1 is false and vice versa for the new system
									  + ',"ESL":"' + CASE WHEN [English First Language] = 1 THEN 'False' ELSE 'True' END + '"'
									  + ',"PersonalToilette":"' + CASE WHEN [Personal Toilette] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"MakesBed":"' + CASE WHEN [Makes Bed] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"AppFeePaid":"' + CASE WHEN [AppFeePaid] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"AdmNotes":"' + COALESCE(CAST([Admission Notes] AS NVARCHAR),'') + '"'
									  + ',"ShortRelationshipname":"' + 'APPLIEDTO' + '"'
									  + ',"Entity2ID":"' + '10000' + '"'
									  + ',"StartDate":"' + CAST(ApplicationDate AS NVARCHAR) + '"'
									  -- If the First Term and First Year are empty, set the end date to one
									  -- month after the start date
									  + CASE WHEN FYAR IS NULL OR FTAR IS NULL
											 THEN ',"EndDate":"' + CAST(DATEADD(month,1,ApplicationDate) AS NVARCHAR) + '"'
										ELSE CASE WHEN EnTerm IS NULL 
											 -- If Enrollment Term is null, this is an active application
											 THEN ''
									  -- Set the end date to the day before the first term the student was/will be here
										ELSE CASE WHEN EnTerm = 1 THEN ',"EndDate":"' 
											+ CASE WHEN ApplicationDate > CAST('08/09/' + CAST(EnYear AS nvarchar) AS DateTime)
													THEN CAST(ApplicationDate AS NVARCHAR) ELSE '08/09/' + CAST(EnYear AS nvarchar) END + '"'
										ELSE CASE WHEN EnTerm = 2 THEN ',"EndDate":"' 
											+ CASE WHEN ApplicationDate > CAST('12/15/' + CAST(EnYear AS nvarchar) AS DateTime)
													THEN CAST(ApplicationDate AS NVARCHAR) ELSE '12/15/' + CAST(EnYear AS nvarchar) END + '"'
										ELSE ',"EndDate":"' 
											+ CASE WHEN ApplicationDate > CAST('04/10/' + CAST(EnYear+1 AS nvarchar) AS DateTime)
													THEN CAST(ApplicationDate AS NVARCHAR) ELSE '04/10/' + CAST(EnYear+1 AS nvarchar) END + '"'
										END END END END
									  + '}'
					  ELSE NULL END AS JSONvalue2,'Entity' AS WriteType
	--SELECT * 
	FROM OPENQUERY(STUDDATA97,'select StudentID AS FriendlyID,FirstName,MiddleName,LastName,PreferedName AS PreferredName,
		[Grad-Year] AS GradYear,Sex AS Gender,Logon,ApplicationDate,[First Year At RVA] AS FYAR,[First Term At RVA] AS FTAR from Students') AS S 
		LEFT JOIN OPENQUERY(STUDDATA97,'SELECT StudentID,MIN(Year) AS EnYear,MIN(Term) AS EnTerm FROM [Enrollment-Term] WHERE [Type of Term] = ''Term'' AND Term > 0 GROUP BY StudentID') AS ET
			ON ET.StudentID = S.FriendlyID
		LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Person}''') AS P
			ON CAST(S.FriendlyID AS NVARCHAR) = P.PersonID
			-- Get admissions records that have a valid application date.  This will prevent nulls from sneaking into the system.
		LEFT JOIN OPENQUERY(STUDDATA97,'SELECT SAI.* FROM [Student Admissions Info] AS SAI INNER JOIN Students AS S ON SAI.StudentID = S.StudentID WHERE NOT ApplicationDate IS NULL') AS ADM
			ON S.FriendlyID = ADM.StudentID
	WHERE (P.EntityID IS NULL AND ((S.GradYear > 2016 AND ET.EnYear IS NULL) OR NOT ET.EnYear IS NULL))
	--**********************************************************************************************
	--PRINT 'Check existing students and update as needed.'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,JSONvalue2,WriteType)
	SELECT				'{"EntityID":"' + COALESCE(CAST(P.EntityID AS NVARCHAR),'-1') 
					  + '","PersonID":"' + CAST(S.FriendlyID AS NVARCHAR)
					  + COALESCE('","FirstName":"' + S.FirstName,'')
					  + COALESCE('","MiddleName":"' + S.MiddleName,'')
					  + COALESCE('","LastName":"' + S.LastName,'')
					  + COALESCE('","PreferredName":"' + S.PreferredName,'')
					  + '","GradYear":"' + CAST(S.GradYear AS NVARCHAR)
					  + '","Gender":"' + (CASE WHEN S.Gender = 0 THEN 'Female' ELSE 'Male' END)
					  + COALESCE('","Logon":"' + S.Logon,'')
					  + '","EntityType":"' + 'Person' + '"'
					  + '}' AS JSONvalue,P.EntityID,
					  CASE WHEN NOT ADM.StudentID IS NULL THEN '{"EntityType":"AdmissionsRequest"'
									  + ',"EntityID":"' + COALESCE(CAST(AR.EntityID AS NVARCHAR),'-1') + '"'
									  + ',"LDHistory":"' + CASE WHEN [Learning Difficulty History] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"ADDHistory":"' + CASE WHEN [ADD History] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"DisciplineHistory":"' + CASE WHEN [Discipline History] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"SpecialMeds":"' + CASE WHEN [Special Medication] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"BedWetHistory":"' + CASE WHEN [Bed Wetting History] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"ESL":"' + CASE WHEN [English First Language] = 1 THEN 'False' ELSE 'True' END + '"'
									  + ',"PersonalToilette":"' + CASE WHEN [Personal Toilette] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"MakesBed":"' + CASE WHEN [Makes Bed] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"AppFeePaid":"' + CASE WHEN [AppFeePaid] = 1 THEN 'True' ELSE 'False' END + '"'
									  + ',"AdmNotes":"' + COALESCE(CAST([Admission Notes] AS NVARCHAR),'') + '"'
									  + ',"ShortRelationshipname":"' + 'APPLIEDTO' + '"'
									  + ',"Entity2ID":"' + '10000' + '"'
									  + ',"StartDate":"' + CAST(ApplicationDate AS NVARCHAR) + '"'
									  -- If the First Term and First Year are empty, set the end date to one
									  -- month after the start date
									  + CASE WHEN FYAR IS NULL OR FTAR IS NULL
											 THEN ',"EndDate":"' + CAST(DATEADD(month,1,ApplicationDate) AS NVARCHAR) + '"'
										ELSE CASE WHEN EnTerm IS NULL 
											 -- If Enrollment Term is null, this is an active application
											 THEN ''
									  -- Set the end date to the day before the first term the student was/will be here
										ELSE CASE WHEN EnTerm = 1 THEN ',"EndDate":"' 
											+ CASE WHEN ApplicationDate > CAST('08/09/' + CAST(EnYear AS nvarchar) AS DateTime)
													THEN CAST(ApplicationDate AS NVARCHAR) ELSE '08/09/' + CAST(EnYear AS nvarchar) END + '"'
										ELSE CASE WHEN EnTerm = 2 THEN ',"EndDate":"' 
											+ CASE WHEN ApplicationDate > CAST('12/15/' + CAST(EnYear AS nvarchar) AS DateTime)
													THEN CAST(ApplicationDate AS NVARCHAR) ELSE '12/15/' + CAST(EnYear AS nvarchar) END + '"'
										ELSE ',"EndDate":"' 
											+ CASE WHEN ApplicationDate > CAST('04/10/' + CAST(EnYear+1 AS nvarchar) AS DateTime)
													THEN CAST(ApplicationDate AS NVARCHAR) ELSE '04/10/' + CAST(EnYear+1 AS nvarchar) END + '"'
										END END END END
									  + '}'
					  ELSE NULL END AS JSONvalue2,'Entity' AS WriteType
	--SELECT *
	--SELECT P.Logon,S.Logon,S.FriendlyID,ADM.StudentID 
	FROM OPENQUERY(STUDDATA97,'select StudentID AS FriendlyID,FirstName,MiddleName,LastName,PreferedName AS PreferredName,
		[Grad-Year] AS GradYear,Sex AS Gender,Logon,ApplicationDate,[First Year At RVA] AS FYAR,[First Term At RVA] AS FTAR from Students') AS S 
		INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Person}''') AS P
			ON CAST(S.FriendlyID AS NVARCHAR) = P.PersonID
		LEFT JOIN OPENQUERY(STUDDATA97,'SELECT SAI.* FROM [Student Admissions Info] AS SAI INNER JOIN Students AS S ON SAI.StudentID = S.StudentID WHERE NOT ApplicationDate IS NULL') AS ADM
			ON S.FriendlyID = ADM.StudentID
		LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Applied}''') AS App
			ON P.EntityID = App.Entity1ID
		LEFT JOIN (SELECT EntityID FROM OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL= ''SELECT *FROM {AdmissionsRequest}''') AS A) AS AR
			ON App.Entity2ID = AR.EntityID
		LEFT JOIN OPENQUERY(STUDDATA97,'SELECT StudentID,MIN(Year) AS EnYear,MIN(Term) AS EnTerm FROM [Enrollment-Term] WHERE [Type of Term] = ''Term'' AND Term > 0 GROUP BY StudentID') AS ET
			ON ET.StudentID = S.FriendlyID
	WHERE 
		(((P.FirstName IS NULL AND NOT S.FirstName IS NULL) OR P.FirstName <> S.FirstName) OR
		((P.MiddleName IS NULL AND NOT S.MiddleName IS NULL) OR P.MiddleName <> S.MiddleName) OR
		((P.LastName IS NULL AND NOT S.LastName IS NULL) OR P.LastName <> S.LastName) OR
		((P.PreferredName IS NULL AND NOT S.PreferredName IS NULL) OR P.PreferredName <> S.PreferredName) OR
		((P.GradYear IS NULL AND NOT S.GradYear IS NULL) OR P.GradYear <> S.GradYear) OR
		((P.Gender IS NULL AND NOT S.Gender IS NULL) OR NOT P.Gender LIKE (CASE WHEN S.Gender = 0 THEN 'Female' ELSE 'Male' END)) OR
		((P.Logon IS NULL AND NOT S.Logon IS NULL) OR P.Logon <> S.Logon))
		AND NOT ADM.StudentID IS NULL
	--**********************************************************************************************

	RETURN 
END